package com.duruikang.LeaveAPI.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.duruikang.LeaveAPI.entity.Leaving;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @author szh、Drik
 * @date 2021-12-18 10:10
 */

public interface LeavingDao extends BaseMapper<Leaving> {

    /**
     * 获取最大请假单号
     * 储存在map中，key为maxLeavingCode
     */
    @Select("SELECT max(leaving_code) AS maxLeavingCode FROM leaving")
    public Map<String, String> getMaxLeavingCode();

    /**
     * 获取某个用户的所有请假单
     * 返回List<Map>
     */
    @Select("   SELECT " +
            "   leaving.leaving_id,leaving.leaving_code,leaving.student_id, " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_reason) AS leaving_reason, " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_type) AS leaving_type, " +
            "   leaving.leaving_destination, leaving.leaving_detail,leaving.leaving_date,leaving.leaving_start,leaving.leaving_end, " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_state) AS leaving_state " +
            "   FROM leaving,student " +
            "   WHERE leaving.student_id = student.student_id AND student.account_id = #{accountId};")
    public List<Map<String, String>> getLeavings(@Param("accountId") String accountId);

    @Select("   SELECT " +
            "   leaving.leaving_id,leaving.leaving_code,leaving.student_id, " +
            "   (SELECT sort.code FROM sort WHERE sort.id = leaving.leaving_reason)AS leaving_reason, " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_type) AS leaving_type, " +
            "   leaving.leaving_destination, leaving.leaving_detail,leaving.leaving_date,leaving.leaving_start,leaving.leaving_end, " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_state) AS leaving_state " +
            "   FROM leaving " +
            "   WHERE leaving.leaving_id=#{leavingId}")
    public Map<String,String> getLeaving(@Param("leavingId") String leavingId);

    @Select("   SELECT " +
            "   leaving.leaving_id,leaving.leaving_code,student.account_id , " +
            "   (SELECT student.name FROM student WHERE leaving.student_id = student.student_id) AS student_name,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_reason) AS leaving_reason,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_type) AS leaving_type,  " +
            "   leaving.leaving_destination, leaving.leaving_detail,leaving.leaving_date,leaving.leaving_start,leaving.leaving_end,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_state) AS leaving_state  " +
            "   FROM leaving,student,teacher " +
            "   WHERE teacher.account_id = #{accountId} AND leaving.student_id = student.student_id AND teacher.college = student.college AND teacher.square = student.square AND leaving.leaving_state = 22 " +
            "   ORDER BY leaving.leaving_state; ")
    public List<Map<String, String>> getExaminingLeavings1(@Param("accountId") String accountId);

    @Select("   SELECT " +
            "   leaving.leaving_id,leaving.leaving_code,student.account_id , " +
            "   (SELECT student.name FROM student WHERE leaving.student_id = student.student_id) AS student_name,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_reason) AS leaving_reason,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_type) AS leaving_type,  " +
            "   leaving.leaving_destination, leaving.leaving_detail,leaving.leaving_date,leaving.leaving_start,leaving.leaving_end,  " +
            "   (SELECT sort.name FROM sort WHERE sort.id = leaving.leaving_state) AS leaving_state  " +
            "   FROM leaving,student,teacher " +
            "   WHERE teacher.account_id = #{accountId} AND leaving.student_id = student.student_id AND teacher.college = student.college AND  teacher.square=9 AND leaving.leaving_type=21 AND leaving.leaving_state = 23 " +
            "   ORDER BY leaving.leaving_state; ")
    public List<Map<String, String>> getExaminingLeavings2(@Param("accountId") String accountId);
}
